package cn.uestc.labhms.modules.hms.service.impl;

import cn.hutool.core.util.StrUtil;
import cn.uestc.labhms.modules.hms.dao.HmsChiEngDao;
import cn.uestc.labhms.modules.hms.dao.XlsxDao;
import cn.uestc.labhms.modules.hms.entity.HmsChiEngEntity;
import cn.uestc.labhms.modules.hms.entity.Vo.AddFieldVo;
import cn.uestc.labhms.modules.hms.entity.Vo.ConditionsFindVo;
import cn.uestc.labhms.modules.hms.entity.Vo.TableNamesVo;
import cn.uestc.labhms.modules.hms.exception.HMSException;
import cn.uestc.labhms.modules.hms.param.ConstParam;
import cn.uestc.labhms.modules.hms.service.TableService;
import cn.uestc.labhms.modules.hms.util.HMSPageUtils;
import cn.uestc.labhms.modules.hms.util.TableUtil;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authz.AuthorizationException;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author Mu.tz
 */
@Service
@Slf4j
public class TableServiceImpl extends ServiceImpl<HmsChiEngDao, HmsChiEngEntity> implements TableService {
    @Autowired
    private HmsChiEngDao hmsChiEngDao;
    @Autowired
    private XlsxDao xlsxDao;
    @Value("${hms.pageSize}")
    private Integer pageSize;
    @Value("${hms.file.download.path}")
    private String modelFilePath;
    private final String entityRefPath = ConstParam.ENTITY_REFE_PATH;
    @Override
    public TableNamesVo getTableNames() {
        List<String> tableNamesC = hmsChiEngDao.getTableNamesCH();
        List<String> tableNamesE = hmsChiEngDao.getTableNamesEN();
        TableNamesVo tableNamesVo = new TableNamesVo();
        tableNamesVo.setTableNamesCH(tableNamesC);
        tableNamesVo.setTableNamesEN(tableNamesE);
        return tableNamesVo;
    }

    @Override
    public List<String> getTableFields(String tableName) {
        List<String> fields = new LinkedList<>();
        List<HmsChiEngEntity> hmsChiEngEntities = baseMapper.selectList(new QueryWrapper<HmsChiEngEntity>()
                .eq("table_name_chi", tableName).select("chi_field"));
        for (HmsChiEngEntity hmsChiEngEntity : hmsChiEngEntities) {
            fields.add(hmsChiEngEntity.getChiField());
        }
        return fields;
    }

    @Override
    public HMSPageUtils getConditionsFind(ConditionsFindVo conditionsFindVo) {
        // 把中文字段全部换成英文字段
        List<String> keysE = new LinkedList<>();
        List<String> fields = conditionsFindVo.getFields();
        if (fields == null) {
            fields = new ArrayList<>();
        }
        for (String key : fields) {
            HmsChiEngEntity hmsChiEngEntity = hmsChiEngDao.selectOne(new QueryWrapper<HmsChiEngEntity>().eq("chi_field", key).last("limit 1"));
            if (ObjectUtils.isEmpty(hmsChiEngEntity) || ObjectUtils.isEmpty(hmsChiEngEntity.getEngField())) {
                throw new HMSException("没有此查询条件，请重新检查查询条件");
            }
            keysE.add(hmsChiEngEntity.getEngField());
        }
        List<String> vals = conditionsFindVo.getVals();
        Map<String, String> keyValMap = TableUtil.parseData(keysE, vals);
        // 获取英文表名
        String tableName = hmsChiEngDao.getTableNameC2E(conditionsFindVo.getTableName());
        // 判断权限
        this.hasPermitted(tableName, "list");
        return TableUtil.getTableData(tableName, keyValMap, conditionsFindVo.getCurPage(), pageSize);
    }

    @Override
    public void insertRowByMap(String tableNameC, Map<String, String> row) {
        // 获取英文表名
        String tableNameE = hmsChiEngDao.getTableNameC2E(tableNameC);
        // 将map中中文的key转为数据库中对应的字段
        Map<String, String> rowE = TableUtil.convertKeyMap2E(row, tableNameE);
        // 判断权限
        this.hasPermitted(tableNameE, "update");
        // 实体类全限定类名
        String entityStr = entityRefPath + "." + getEntityStr(tableNameE);
        // 获取主键字段名
        try {
            Class<?> clazz = Class.forName(entityStr);
            String id = this.getIdNameFromEntity(clazz);
            String idVal = rowE.get(id);
            if (StringUtils.isEmpty(idVal)) {
                throw new HMSException(id + "为主键，不能为空");
            }
            xlsxDao.insertRowByMap(tableNameE, rowE);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new HMSException(e.getMessage(), e);
        }
    }

    @Override
    public void updateRowByMap(String tableNameC, Map<String, String> row) {
        // 获取英文表名
        String tableNameE = hmsChiEngDao.getTableNameC2E(tableNameC);
        // 将map中中文的key转为数据库中对应的字段
        Map<String, String> rowE = TableUtil.convertKeyMap2E(row, tableNameE);
        // 判断权限
        this.hasPermitted(tableNameE, "update");
        // 实体类全限定类名
        String entityStr = entityRefPath + "." + getEntityStr(tableNameE);
        // 获取主键字段名
        try {
            Class<?> clazz = Class.forName(entityStr);
            String id = this.getIdNameFromEntity(clazz);
            String idVal = rowE.get(id);
            if (StringUtils.isEmpty(idVal)) {
                throw new HMSException(id + "为主键，不能为空");
            }
            xlsxDao.updateRowByMap(tableNameE, rowE, id, idVal);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new HMSException(e.getMessage(), e);
        }
    }

    @Override
    public int deleteRowById(String tableNameC, String id) {
        // 获取英文表名
        String tableNameE = hmsChiEngDao.getTableNameC2E(tableNameC);
        // 判断权限
        this.hasPermitted(tableNameE, "update");
        // 实体类全限定类名
        String entityStr = entityRefPath + "." + getEntityStr(tableNameE);
        // 获取主键字段名
        try {
            Class<?> clazz = Class.forName(entityStr);
            String idName = this.getIdNameFromEntity(clazz);
            if (StringUtils.isEmpty(id)) {
                throw new HMSException(id + "为主键，不能为空");
            }
            return xlsxDao.deleteRowById(tableNameE, idName, id);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new HMSException(e.getMessage(), e);
        }
    }

    @Override
    public String getIdName(String tableNameC) {
        // 获取英文表名
        String tableNameE = hmsChiEngDao.getTableNameC2E(tableNameC);
        // 判断权限
        this.hasPermitted(tableNameE, "list");
        // 实体类全限定类名
        String entityStr = entityRefPath + "." + getEntityStr(tableNameE);
        try {
            Class<?> clazz = Class.forName(entityStr);
            String idName = this.getIdNameFromEntity(clazz);
            return hmsChiEngDao.getFieldE2C(tableNameE, idName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new HMSException(e.getMessage(), e);
        }
    }
    @Override
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public void addXlsxField(AddFieldVo addFieldVo) {
        // 将字段信息和表名插入hms_chi_eng中
        String tableNameC = addFieldVo.getTableNameC();
        String tableNameE = hmsChiEngDao.getTableNameC2E(tableNameC);
        if (StringUtils.isEmpty(tableNameE)) {
            throw new HMSException("此中文表名不对应任何英文表名，请输入数据库中存在的中文表名（数据库中存在的中文表名可通过其它API接口请求获得）");
        }
        HmsChiEngEntity hmsChiEngEntity = new HmsChiEngEntity();
        hmsChiEngEntity.setChiField(addFieldVo.getFieldC());
        hmsChiEngEntity.setEngField(addFieldVo.getFieldE());
        hmsChiEngEntity.setTableNameChi(tableNameC);
        hmsChiEngEntity.setTableNameEng(tableNameE);
        hmsChiEngDao.insert(hmsChiEngEntity);
        // 创建新字段
        xlsxDao.addXlsxField(tableNameE, addFieldVo.getFieldE(), addFieldVo.getFieldLen());
        // 修改excel文件
        // 根据表名获取字段数量
        int idx = hmsChiEngDao.selectCount(new QueryWrapper<HmsChiEngEntity>()
                .eq("table_name_eng", hmsChiEngEntity.getTableNameEng()));
        // 获取坐标
        String coordinate = idx2Coordinate4Excel(idx) + "1";
        reviseExcel(modelFilePath, hmsChiEngEntity.getTableNameChi(), coordinate, hmsChiEngEntity.getChiField());
    }

    /**
     * Excel文件增加一个字段
     * @param path 文件路径（仅支持xlsx文件）
     * @param sheetName 表单名
     * @param coordinate 坐标，e.g. B4
     * @param value 插入的值
     */
    @Transactional(rollbackFor = Exception.class)
    public void reviseExcel(String path, String sheetName, String coordinate, String value) {
        //根据路径获取文件
        File file = new File(path);
        //定义输入流对象
        FileInputStream excelFileInputStream;
        try {
            excelFileInputStream = new FileInputStream(file);
            // 拿到文件转化为JavaPoi可操纵类型
            Workbook workbook = WorkbookFactory.create(excelFileInputStream);
            excelFileInputStream.close();
            ////获取excel表格
            Sheet sheet = workbook.getSheet(sheetName);
            //获取单元格的row和cell
            CellAddress address = new CellAddress(coordinate);
            // 获取行
            Row row = sheet.getRow(address.getRow());
            // 获取列
            Cell cell = row.createCell(address.getColumn());
            //设置单元的值
            cell.setCellValue(value);
            //写入数据
            FileOutputStream excelFileOutPutStream = new FileOutputStream(file);
            workbook.write(excelFileOutPutStream);
            excelFileOutPutStream.flush();
            excelFileOutPutStream.close();
            log.info("指定单元格设置数据写入完成");
        } catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
            throw new HMSException("模板文件写入出错");
        }
    }

    /**
     * 将数字索引转换为字母索引，e.g. 28 <-> AB
     * @param idx 1, 2, 3 ...
     * @return A ... AA, AB, ...
     */
    private String idx2Coordinate4Excel(int idx) {
        StringBuilder sb = new StringBuilder();
        while (idx != 0) {
            int remain = (idx - 1) % 26;
            idx = (idx - 1) / 26;
            sb.insert(0, (char) (remain + 'A'));
        }
        return sb.toString();
    }

    /**
     * 通过model注解获取主键名(与数据库主键同名)
     * @param clazz 实体类型类
     * @return 主键名
     */
    private String getIdNameFromEntity(Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            boolean fieldHasTableId = field.isAnnotationPresent(TableId.class);
            if (fieldHasTableId) {
                String idName = field.getName();
                return StrUtil.toUnderlineCase(idName);
            }
        }
        throw new HMSException("【源码级别错误】该实体类不存在主键注解，无法解析主键字段名，请联系开发人员。");
    }

    /**
     * 判断是否有操作权限
     * @param tableName 英文表名
     * @param type 操作类型
     */
    private void hasPermitted(String tableName, String type) {
        Subject subject = SecurityUtils.getSubject();
        String perm = "hms:" + tableName + ":" + type;
        if (!subject.isPermitted(perm)) {
            throw new AuthorizationException("无权限");
        }
    }

    /**
     * 获取模型类简单路径类名
     * @param tableName 英文表名
     * @return
     */
    private String getEntityStr(String tableName) {
        String entityStr = null;
        if (tableName.length() >= 5 && "xlsx_".equals(tableName.substring(0, 5))) {
            tableName = tableName.substring(5);
        }
        entityStr = StrUtil.toCamelCase(tableName);
        entityStr = StrUtil.upperFirst(entityStr);
        entityStr += "Entity";
        return entityStr;
    }
}
